package com.gohi.main.store.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.TransactionException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.gohi.core.service.impl.DefaultService;
import com.gohi.main.util.MD5;
import com.gohi.main.util.StringUtil;
import com.gohi.main.util.Utils;
import com.gohi.main.util.global.GloblConfig;
import com.gohi.main.store.hbm.BaseStoreClass;
import com.gohi.main.store.hbm.ShopgooBeautician;
import com.gohi.main.store.hbm.ShopgooBrand;
import com.gohi.main.store.hbm.ShopgooGroupbuy;
import com.gohi.main.store.hbm.ShopgooStore;
import com.gohi.main.store.hbm.ShopgooStoreClass;
import com.gohi.main.store.hbm.jdbcMapper.BeauticianMapper;
import com.gohi.main.store.hbm.jdbcMapper.BeauticianMappers;
import com.gohi.main.store.hbm.jdbcMapper.GroupByBeautMapper;
import com.gohi.main.store.hbm.jdbcMapper.GroupbuyMapper;
import com.gohi.main.store.hbm.jdbcMapper.StoreClassMapper;
import com.gohi.main.store.hbm.jdbcMapper.StoreMapper;
import com.gohi.main.store.service.IStoreService;

public class StoreServiceImpl extends DefaultService<Object, String> implements
		IStoreService {

	@Override
	public List<ShopgooStoreClass> getStoreClassByparentID(String id)
			throws TransactionException {
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		String sql = "select * from shopgoo_store_class where parent_class_id="
				+ id + " ORDER BY class_sort desc";
		
		return jdbc.query(sql, new StoreClassMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BaseStoreClass> getStoreClass(){
		// TODO Auto-generated method stub
		List<ShopgooStoreClass> classlist = new ArrayList<ShopgooStoreClass>();
		List<BaseStoreClass> list = new ArrayList<BaseStoreClass>();
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		//查询最热的
		String 	sql="select * from shopgoo_store_class where parent_class_id!=0 and class_settle=1 ORDER BY class_sort desc";
		BaseStoreClass base = new BaseStoreClass();
		base.setList(jdbc.query(sql, new StoreClassMapper()));
		base.setStoreclass(new ShopgooStoreClass("推荐"));
		list.add(base);
		//下面开始挨个查询
		sql = "select * from shopgoo_store_class where parent_class_id=0 ORDER BY class_sort desc";// 查询父类根据排序ID来排序
		classlist = jdbc.query(sql, new StoreClassMapper());
		
		//遍历查询下面所有的分类信息
		for (ShopgooStoreClass c : classlist) {
			sql = "select * from shopgoo_store_class where parent_class_id="
					+ c.getClass_id() + " ORDER BY class_sort desc";
			 base = new BaseStoreClass();
			base.setList(jdbc.query(sql, new StoreClassMapper()));
			base.setStoreclass(c);
			list.add(base);
		}


		//System.err.println(classlist.size());
		return list;
	}

	@Override
	public List<ShopgooStore> queryStore(ShopgooStore store) {
		// TODO Auto-generated method stub
		StringBuffer buf =new StringBuffer("select st.store_id,st.store_name,st.temai_flag,st.person_consume,st.comment_count,st.store_score,logo,address,");
		//查询位置
		buf.append("ROUND(6378.138*2*ASIN(SQRT(POW(SIN(("+store.getLat()+"*PI()/180-lat*PI()/180)/2),2)+COS("+store.getLat()+"*PI()/180)");
		buf.append("*COS(lat*PI()/180)*POW(SIN(("+store.getLng()+"*PI()/180-lng*PI()/180)/2),2)))*1000)/1000 AS km ");
		buf.append("from shopgoo_store as st where st.is_audit=2 and st.store_state=2  ");
		//条件追加
		if(store.getClass_id()!=null &&store.getClass_id()>0){
			buf.append(" and st.s_class_id="+store.getClass_id());
		}
		
		if(store.getCity_id()!=null &&store.getCity_id()>0){
			buf.append(" and st.city_id="+store.getCity_id());
		}
		//商户名字查询
		if(!Utils.show(store.getStore_name()).equals("")){
			buf.append(" and st.store_name  like '%"+store.getStore_name()+"%' ");
		}
		
		if(!Utils.show(store.getBrand_id()).equals("") && store.getBrand_id()!=0){
			buf.append(" and st.brand_id="+store.getBrand_id());
		}
		
		//价格查询
		if(!Utils.show(store.getMinprice()).equals("") ){
			buf.append(" and st.person_consume BETWEEN "+store.getMinprice() +" and "+store.getMaxprice());
		}
		
		
		//价格查询
		if(Utils.show(store.getTemai_flag()).equals("1")){
			buf.append(" and st.temai_flag>0");
		}			
				
				
		//行政区域查询
		if(!Utils.show(store.getMall_id()).equals("") && store.getMall_id()!=0){
			if( !Utils.show(store.getArea_id()).equals("")){
				buf.append(" and st.area_id="+store.getMall_id());
			}else{
				buf.append(" and st.mall_id="+store.getMall_id());
			}
			
		}
		
		//商圈查询
		if(!Utils.show(store.getArea_business_id()).equals("") && store.getArea_business_id()!=0){
			buf.append(" and st.area_business_id="+store.getArea_business_id());
		}
		
		
		//System.err.println(store.getOrderby());
		//结果排序
		buf.append(" order by ");
		if(!Utils.show(store.getOrderby()).equals("") ){
			buf.append(store.getOrderby()+" "+store.getSorttype());
			//不等于路径
			if(!store.getOrderby().equals("km")){
				buf.append(", km asc");
			}
		}else{
			buf.append(" km asc");
		}
		
		buf.append(" limit "+store.getLimit()*GloblConfig.PAGE_SIZE +","+GloblConfig.PAGE_SIZE);
		//System.err.println(buf.toString());
		
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
	
		//jdbc.query(jdbc, rse)
		//读取
		//System.err.println("测试："+buf.toString());
		SqlRowSet rs= jdbc.queryForRowSet(buf.toString());
		
		//System.err.println("测试："+buf.toString());
		List<ShopgooStore> list = new ArrayList<ShopgooStore>();
		while(rs.next()){
			ShopgooStore  st =new ShopgooStore();
			st.setStore_id(rs.getInt("store_id"));
			st.setKm(rs.getString("km"));
			st.setStore_name(rs.getString("store_name"));
			//st.setPerson_consume(rs.getInt("person_consume"));
			int z = rs.getInt("store_score");
			//int score = Integer.parseInt(()+"");
			st.setStore_score(Math.round(z==0?5:z/ rs.getInt("comment_count")));
			st.setLogo(rs.getString("logo"));
			st.setAddress(rs.getString("address"));
			st.setTemai_flag(rs.getInt("temai_flag"));//特卖标志信息
			//System.err.println(row.getString("area_name"));
			list.add(st);
		}
		
		
		return list;
	}

	@Override
	public ShopgooStore getStoreById(String id) {
		// TODO Auto-generated method stub
		String sql ="SELECT f.*,count(b.store_id) as counts from shopgoo_store as f , shopgoo_beautician as b where f.store_id="+id+" and b.status=1 and b.store_id="+id;
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
	//	System.err.println(sql);
		return (ShopgooStore) jdbc.queryForObject(sql, new StoreMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ShopgooGroupbuy> queryGroup(ShopgooGroupbuy group) {
		// TODO Auto-generated method stub
		StringBuffer  sql = new StringBuffer("select g.*,s.address as address from shopgoo_groupbuy as g, shopgoo_store as s  where 1=1 and  s.is_audit=2 and s.store_state=2 and g.is_on_shelf=1  and g.store_id=s.store_id and g.is_audit=2 ");
		if(group!=null){
			//ID查询
			if(group.getGroup_id()!=null &&  group.getGroup_id()>0){
				sql.append(" and g.group_id="+group.getGroup_id());
			}
			
			//商铺查询
			if(group.getStore_id()!=null){
				sql.append(" and g.store_id="+group.getStore_id());
			}
			
			//区域查询
			if(group.getCity_id()!=null && group.getCity_id()>0){
				sql.append(" and g.city_id="+group.getCity_id());
			}
			
			
			//区域查询
			if(group.getClass_id()!=null && group.getClass_id()>0){
				sql.append(" and g.s_class_id="+group.getClass_id());
			}
			
			//品牌查询
			if(!Utils.show(group.getBrand_id()).equals("") && group.getBrand_id()!=0){
				sql.append(" and s.brand_id="+group.getBrand_id());
			}
			
			//商圈查询
			if(!Utils.show(group.getArea_business_id()).equals("") && group.getArea_business_id()!=0){
				sql.append(" and s.area_business_id="+group.getArea_business_id());
			}
			
			
			//价格查询
			if(!Utils.show(group.getMinprice()).equals("") ){
				sql.append(" and g.group_price BETWEEN "+group.getMinprice() +" and "+group.getMaxprice());
			}	
			
			
			//名称查询
			if(!Utils.show(group.getGroup_name()).equals("") ){
				sql.append(" and g.group_name like '%"+group.getGroup_name() +"%'");
			}	
			
			//行政区域查询
			if(!Utils.show(group.getMall_id()).equals("") && group.getMall_id()!=0){
				if( !Utils.show(group.getArea_id()).equals("")){
					sql.append(" and s.area_id="+group.getMall_id());
				}else{
					sql.append(" and s.mall_id="+group.getMall_id());
				}
				
			}
			
			//结果排序
			sql.append(" order by ");
			//System.err.println(group.getOrderby());
			if(!Utils.show(group.getOrderby()).equals("") ){
				sql.append(group.getOrderby()+" "+group.getSorttype());
			}else{
				sql.append(" buyer_num asc");
			}
			if(group.getGroup_id()==null){
				sql.append(" limit "+group.getLimit()*GloblConfig.PAGE_SIZE +","+GloblConfig.PAGE_SIZE);
			}
			//System.err.println(group.getLimit());

		}
		//System.err.println(sql.toString());
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		return jdbc.query(sql.toString(), new GroupbuyMapper());
	}

	@Override
	public List<ShopgooBeautician> queryBeauticianByStroe(String stroeid) {
		// TODO Auto-generated method stub
		String sql ="select * from shopgoo_beautician where store_id="+stroeid+" and  status=1 ORDER BY  orders desc";
		//System.err.println(sql);
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		
		return jdbc.query(sql, new BeauticianMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ShopgooBeautician> queryBeauticianByGroupId(String id) {
		String sql="SELECT be.*,price.price as baojia FROM shopgoo_beauticianprice as price ,shopgoo_beautician as be  where be.beautician_id=price.beautician_id and be.status=1 and price.groupbuy_id="+id;
	//	System.err.println(sql);
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		return jdbc.query(sql, new BeauticianMappers());
	}

	@Override
	public ShopgooBeautician queryBeauticianByid(String id) {
		String sql ="select * from  shopgoo_beautician  where  status=1 and  beautician_id="+id;
		return (ShopgooBeautician) getDAO().getJdbcTemplate().queryForObject(sql, new BeauticianMapper());
		
	}

	@Override
	public List<ShopgooGroupbuy> queryGroupbuyByBeauticId(String id) {
		String sql ="SELECT be.*,price.price as baojia FROM shopgoo_beauticianprice as price ,shopgoo_groupbuy as be  where be.group_id=price.groupbuy_id and be.is_on_shelf=1 and  be.is_audit=2 and price.beautician_id="+id;
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		return jdbc.query(sql.toString(), new GroupByBeautMapper());
	}
	//查询我收藏的
	@Override
	public List<ShopgooStore> queryFavStore(String userid) {
		// TODO Auto-generated method stub
		String sql ="select   fav.*,store.* from shopgoo_favorites as fav,shopgoo_store as store where fav.fav_type='store' and store.store_id=fav.fav_id and fav.member_id="+userid+" order by fav.fav_time desc";
		SqlRowSet rs= getDAO().getJdbcTemplate().queryForRowSet(sql.toString());
		
		  //System.err.println("测试："+buf.toString());
			List<ShopgooStore> list = new ArrayList<ShopgooStore>();
			while(rs.next()){
				ShopgooStore  st =new ShopgooStore();
				st.setStore_id(rs.getInt("store_id"));
				//st.setKm(rs.getString("km"));
				st.setStore_name(rs.getString("store_name"));
				//st.setPerson_consume(rs.getInt("person_consume"));
				//积分数
				int z = rs.getInt("store_score");
				//int score = Integer.parseInt(()+"");
				st.setStore_score(Math.round(z==0?5:z/ rs.getInt("comment_count")));

				st.setLogo(rs.getString("logo"));
				st.setAddress(rs.getString("address"));
				st.setGroupbuy_num(rs.getInt("groupbuy_num"));
				
				//System.err.println(row.getString("area_name"));
				list.add(st);
			}
			return list;
		
		//return 	 getDAO().getJdbcTemplate().query(sql, new StoreMapper());
	}

	@Override
	public int addFav(String type, String favid, String userid) {
		// TODO Auto-generated method stub
		String sql ="SELECT count(*) as result from shopgoo_favorites where member_id="+userid+" and fav_type='"+type+"' and fav_id="+favid;
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		if(jdbc.queryForInt(sql)==0){
		 sql ="INSERT INTO shopgoo_favorites (member_id, fav_id, fav_type, fav_time) VALUES (?, ?, ?, '"+StringUtil.getTimeSimple()+"')";
		  Object[] params = new Object[] {userid,favid,type};
		  return getDAO().getJdbcTemplate().update(sql,params);
		}else{
			sql = "delete  from  shopgoo_favorites  where member_id="+userid+" and fav_type='"+type+"' and fav_id="+favid;
		//	System.err.println(sql); 
			 getDAO().getJdbcTemplate().update(sql);
			 return 0;
		}
	
	}

	@Override
	public List<ShopgooBrand> queryAllBrand() {
		String sql ="select * from shopgoo_brand ORDER BY brand_sort desc";
	SqlRowSet rs= getDAO().getJdbcTemplate().queryForRowSet(sql);
	List<ShopgooBrand> list = new ArrayList<ShopgooBrand>();
	while(rs.next()){
	ShopgooBrand  st =new ShopgooBrand();
	st.setBrand_id(rs.getInt("brand_id"));//ID
	st.setBrand_name(rs.getString("brand_name"));//品牌名字
	list.add(st);
	}
		return list;
	}

	@Override
	public int favThere(String type, String favid, String userid) {
		// TODO Auto-generated method stub
		String sql ="SELECT count(*) as result from shopgoo_favorites where member_id="+userid+" and fav_type='"+type+"' and fav_id="+favid;
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
		
		return jdbc.queryForInt(sql);
	}

}
